﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class UbicacionDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeUbicacion(ByVal pClave As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM ubicacion " & _
                                            "WHERE Clave = ?Clave;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Clave", pClave)
                    myConn.Open()
                    Return (Convert.ToInt16(myComm.ExecuteScalar()) > 0)
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
        End Try
    End Function

    Public Function registraUbicacion(ByVal pUbicacion As UbicacionBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO ubicacion(Clave, Descripcion, Status) " & _
                                            "VALUES (@Clave, @Descripcion, @Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Clave", pUbicacion.Clave)
                    myComm.Parameters.AddWithValue("@Descripcion", pUbicacion.Descripcion)
                    Select Case pUbicacion.Status
                        Case UbicacionBE.EstatusUbicacion.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case UbicacionBE.EstatusUbicacion.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaUbicacion(ByVal pUbicacion As UbicacionBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM ubicacion " &
                                            "WHERE IdUbicacion <> @IdUbicacion " &
                                                "AND Clave = @Clave;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdUbicacion", pUbicacion.IdUbicacion)
                    myComm.Parameters.AddWithValue("@Clave", pUbicacion.Clave)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con la clave especificada." & vbCrLf
                        Return False
                    End If


                    myComm.CommandText = "UPDATE ubicacion " & _
                                            "SET Clave = @Clave, " & _
                                                "Descripcion = @Descripcion, " & _
                                                "Status = @Status " & _
                                            "WHERE IdUbicacion = @IdUbicacion; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Clave", pUbicacion.Clave)
                    myComm.Parameters.AddWithValue("@Descripcion", pUbicacion.Descripcion)
                    Select Case pUbicacion.Status
                        Case UbicacionBE.EstatusUbicacion.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case UbicacionBE.EstatusUbicacion.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@IdUbicacion", pUbicacion.IdUbicacion)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getUbicacion(ByVal IdUbicacion As Integer) As UbicacionBE
        Dim tmpUbicacion As UbicacionBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdUbicacion, Clave, Descripcion, Status " & _
                                            "FROM ubicacion " & _
                                            "WHERE IdUbicacion = @IdUbicacion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdUbicacion", IdUbicacion )
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpUbicacion = UbicacionFact.Build(myDR)
                        Else
                            tmpUbicacion = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpUbicacion

        Catch ex As Exception
            Throw
        Finally
            tmpUbicacion = Nothing
        End Try
    End Function

    Public Function getListadoUbicacion(ByVal estatus As Enumeraciones.estatusParaBusquedas) As List(Of UbicacionBE)
        Dim UbicacionLs As List(Of UbicacionBE) = New List(Of UbicacionBE)
        Dim strQryStatus As String = ""
        Select Case estatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No se aplica filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                strQryStatus &= " WHERE Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                strQryStatus &= " WHERE Status = 'I' "
        End Select
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdUbicacion, Clave, Descripcion, Status " & _
                                           "FROM ubicacion " &
                                           strQryStatus & " ;"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Ubicacion As UbicacionBE = UbicacionFact.Build(myDR)
                            If (Not Ubicacion Is Nothing) Then
                                UbicacionLs.Add(Ubicacion)
                            End If
                            Ubicacion = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return UbicacionLs
        Catch ex As Exception
            Throw
        Finally
            UbicacionLs = Nothing
            strQryStatus = Nothing
        End Try
    End Function

    Function cambiaStatus(ByVal pId As Integer, ByVal psetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE ubicacion " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdUbicacion = @IdUbicacion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdUbicacion", pId)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region

    

End Class
